package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBCustomer implements IFDBCustomer {
	
    private  Connection con;
    /** Creates a new instance of DBEmployee */
    public DBCustomer() {
      con = DbConnection1.getInstance().getDBcon();
    }

	public ArrayList<Customer> getAllCustomers(boolean retriveAssociation) {
		// Auto-generated method stub
		 return miscWhere("", retriveAssociation);
	}

	public Customer findCustomer(int customerId) {
		//Auto-generated method stub
		 String wClause = "  customerID = '" + customerId + "'";
	        return singleWhere(wClause);
	}
	
	public Customer findCustomerWithPhoneNumber(String phoneNumber) {
		// TODO Auto-generated method stub
		String wClause = "  cusPhoneNo = '" + phoneNumber + "'";
        return singleWhere(wClause);
	}
	

	public Customer searchCustomerName(String name) {
		//Auto-generated method stub
        String wClause = "cusname like '%" + name + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause);
	}

	public Customer searchCustomerId(String name) {
		//Auto-generated method stub
		String wClause = "customerID = '" + name + "'";
        System.out.println("Search Customer: " + wClause);
        return singleWhere(wClause);
	}

	public int insertCustomer(Customer cus) throws Exception {
		// Auto-generated method stub
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(customerID) from customer");
        nextID = nextID + 1;
        System.out.println("next customerID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO customer(customerid, cusname, cusaddress, cuszip, cusphoneno, cusEmail, type)  VALUES('"+
	  		nextID + "','" +
			cus.getName()  + "','"  +
		    cus.getAddress()  + "','"  +
		    cus.getZipCode()  + "','"  +
		    cus.getPhoneNo()  + "','"  +
		    cus.getEmail()  + "','"  +
            cus.getType() + "')";
                     

       System.out.println("insert : " + query);
      try{ // insert new customer +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Customer ikke oprettet");
          throw new Exception ("Customer is not inserted correct");
       }
       return(rc);
    }


	public int updateCustomer(Customer cus) {
		//Auto-generated method stub
		Customer cusObj  = cus;
		int rc=-1;

		String query="UPDATE customer SET "+
		 	  "cusName ='"+ cusObj.getName()+"', "+
		 	  "CusAddress ='"+ cusObj.getAddress() + "', " +
                          "cusZip ='"+ cusObj.getZipCode() + "', " +
                          "cusPhoneNo ='"+ cusObj.getPhoneNo() + "', " +
                          "cusEmail ='"+ cusObj.getEmail() + "', " +
                          "Type ='"+ cusObj.getType() + "' " +
		          " WHERE customerID = '"+ cusObj.getCustomerId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteCustomer(int customerID)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM customer WHERE customerID = '" +
				customerID + "'";
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}
	
	private Customer singleWhere(String wClause)
    {	
        ResultSet results;
        Customer cusObj = new Customer();
        String query =  buildQuery(wClause);
        System.out.println("DbCustomer -singleWhere " + query); 
        try{ // read from project
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       cusObj = buildCustomer(results);
               //missing the test on retriveassociation
               
           }//end if
	   stmt.close();
	}//slut try	
	catch(Exception e){
	   System.out.println("Query exception - select customer : "+e);
	   e.printStackTrace();
        }
	return cusObj;

    
    }
	
	 //miscWhere is used when more than one laaner is selected and build
    private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbCustomer " + query); 
  		try{ // read from project
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
		     	 Customer cusObj = new Customer();
			 cusObj = buildCustomer(results);	
                         list.add(cusObj);
                         //missing tes on retriveAssociation
			}//end while
			stmt.close();
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select customer : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Customer buildCustomer(ResultSet results)
    {
       Customer cusObj = new Customer();
       
         try{
               cusObj.setCustomerId(results.getInt("customerID"));
               cusObj.setName(results.getString("cusname"));
               cusObj.setAddress(results.getString("cusAddress"));
               cusObj.setZipCode(results.getString("cusZip"));
               cusObj.setCity(results.getString("city"));
               cusObj.setPhoneNo(results.getString("cusPhoneNo"));
               cusObj.setEmail(results.getString("cusEmail"));
               cusObj.setType(results.getString("type"));
               
          }
         catch(Exception e)
         {
             System.out.println("building customer object");
         }
        
        return cusObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT customerID, cusName, cusAddress, cusZip, city, cusPhoneNo, cusEmail, type FROM customer,zipcode where cusZip=ZipCode"; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}

	

}
